Release 10.1A: OpenEdge Development:
Progress Dynamics Basic Development

Strategies for SmartDataObject query definition

A major purpose of SDOs is to make it easier for you to put all the appropriate business logic for dealing with a database table or set of related database tables in one place, so that it is always executed whenever that table is used. For this reason, it is important not to define more SDOs than you need. Keep in mind that you can attach many different visual objects to an SDO under different circumstances. Different fields can be displayed or enabled, and other aspects of the SDO can be customized. There is, generally, no need to have a number of different SDOs for these different situations. Though no set of guidelines will cover all application situations, the following suggestions will give you something to think about as you design your application.

Consider these two basic principles:

Ideally, there should be just one SDO for a database table.
That SDO should contain all the fields that any client object would ever use from that table.

If there is just one SDO for each database table, then there is no question about where the update logic for the table goes, and it will be executed whenever that SDO is used. In most cases, there should be only one table in each SDO, as well.

Joins are problematic. You should never use an SDO to update multiple tables in a one-to-many relationship, such as an Order and its OrderLine records. A one-to-many join will not provide a useful display, since the parent information will be repeated for every child record. It is also not practical to update the parent information in this kind of setup, again because it is repeated for each child record. For example, if you define an SDO with the following query, the Order information will be repeated for every OrderLine an Order has:

FOR EACH Order, EACH OrderLine OF Order

This result will not look attractive, and it makes the Order information more or less impossible to update. If you allow changes to Order fields, then a modification to an Order record through the SDO will appear to have changed just one of many instances of that Order information. However, there is only one Order record in the database, and that one record has been changed.

So, updateable joins should be restricted to one-to-one joins between tables that are almost always referenced together. Chapter 11, "Building Advanced Business Logic in a Progress Dynamics Application," discusses alternatives for logic that goes beyond a single table.

Another problem with joins is that if both tables in a joined SDO are updateable and an Add operation is done, a new row is created for both tables even when this might not be desired.

So, if an SDO has a join, you should enable only one table in the join so that the SDO can have the update logic for that one table only. The one reasonable exception to this would be if the two tables are really always updated (and created) together, such as header and detail information for a Customer.

You might immediately (and reasonably) object that these guidelines are too restrictive. For one thing, a join is often necessary because the main table in a query contains key values for other tables. These key values are not meaningful for users looking at the data. For example, an Order SDO that just shows CustNum will not be helpful to the user without the Customer Name. This is not a problem: you can go ahead and join Order to Customer and include the Name field from Customer in the Order SDO. However, do not enable that field for update. That way the Customer.Name field can be displayed without you worrying about Customer update logic in that object. An object can contain read-only joins in several directions if necessary to display meaningful information in this way. For example, the Order SDO could also include SalesRep OF Order in addition to Customer OF Order to show (but not update) the SalesRep RepName field.

As discussed in Chapter 7, "Building Progress Dynamics Lookups and Combos," you can add Progress SmartDataFields in the form of dynamic lookups and combos to any viewer in your application. You can use them not only to display all the possible valid values for a field, but also to display other fields that help identify the value in a meaningful way. For example, you can use them to show the Customer.Name for an Order as well as the CustNum field, or the Sales Rep Name field in addition to the SalesRep initials. It is important to understand in this regard how Progress Dynamics supports these special fields, and also what the product direction is.

In Progress Dynamics, a dynamic lookup or combo is populated from the database when it is displayed. This means that when you display a record in a viewer that includes a dynamic lookup or combo, the values from the related table that are displayed along with the foreign key value are retrieved from the database (therefore from the server in a distributed application) at that time.

For example, you can avoid a server access to obtain the Customer.Name for display in an Order viewer if that field has already been added to the Order SDO through a read-only join to the Customer table. For this reason, you should include such fields in your SDOs so that they will be available as the product is optimized, without changes to existing applications being required.

In addition, you might want to show linked fields, such as Customer.Name or SalesRep.Name, in a browser where the lookups and combos are not available. This is another reason to include them in the SDO definition.

The time to modify SDO definitions in this way is after you have generated SDOs in the Object Generator. As was described in Chapter 5, "Using the Object Generator," you can specify that you only want to generate SDOs, not dynamic viewers and browsers, when you run the Object Generator. This gives you the ability to modify the query definitions and field lists before generating other objects. The Object Generator will add joins to other tables automatically if your schema definition conforms to the naming conventions described in Chapter 2, " Database Design Principles in Progress Dynamics." For an existing database, you must define the relationships and the appropriate descriptive fields to add to SDOs yourself.

To modify an Order SDO example, to show its use and also its limitations, and then show you an alternative:

After you have generated SDOs in the Object Generator, open them in the AppBuilder to edit them.
After opening an SDO, double-click on its design window to bring up its property sheet, as shown:



Choose the Query button to display the Query Builder for the Order SmartDataObject.
Select the Order, Customer, and SalesRep tables, as shown:



Choose OK.
Choose the Fields button to bring up the Column Editor for the SDO. Note that properties of the fields reflect the data field definitions (Entity Maintenance) rather than the database schema.
Add the Customer.Name and SalesRep.RepName fields to the SDO’s column list, but make them nonupdateable. Note that the SalesRep and CustNum fields are also marked nonupdateable. This is to prevent a user from modifying these key fields. However, if you allow these foreign key fields to be modified, the SDO will properly refresh itself to join to the new related record, as shown:



Note: Whether the key fields are updateable or not is up to you as the application designer.

When you build a viewer for this SDO and then add it to a window along with a dynamic browser and toolbar, you can see the Customer and SalesRep names along with their key values. Because they are not updateable, the SDO remains clearly an order SDO for update purposes, and the customer and salesrep information can never be modified using it, as shown:

Another objection to having just a single SDO for each table could be that retrieving the entire SDO data set (and especially the entire main table plus various fields from other joined tables) is inefficient when the user wants to browse just a few fields to select a particular record. In this case, it is perfectly reasonable to define an additional read-only SDO for the table, with no joins to other tables, and with the field list restricted to just the fields needed for the browser or other uses to which this object is put. You can then use this object in a browse window or other situation where an efficient browser is needed.

Note, however, that the dynamic lookup can often satisfy the requirement for browsing records without the use of an SDO at all.

Another point mentioned earlier with regard to the field list in an SDO is that it should generally include all the fields from the primary table that are used on the client. Sometimes tables contain a very large number of fields. This can lead to problems defining SDOs for them because of limits to the statement length in Progress, when the DEFINE TEMP-TABLE statement for the SDO becomes excessively long. In some cases, a table can have fields that are used by the business logic (in calculations, for example) but never viewed or updated on the client. Such fields do not normally need to be part of the SDO definition. The SDO will find the database record for an SDO temp-table record being updated when the update is returned to the server. You can modify or look at any other fields in the database as part of the SDO’s update logic.

In other cases, where the field list is exceptionally long, different parts of the record are used in one kind of operation and other parts of the record in another.

Note: If this is the case, then it is very likely that splitting up the table into multiple tables would improve the database design and the efficiency of the application, but it might not be practical to do this with an existing database.

If this is true, then you can define multiple SDOs to view and update different parts of the record so that the whole record is not transferred across the AppServer connection. With the logic procedure that is part of the SDO in the Progress Dynamics architecture (see the "Standard validation procedures for SDOs" section for more details), you can define a single logic procedure to be the custom super procedure for multiple SDOs, if that is appropriate, to share the business logic between SDOs.

These guidelines will not satisfy every need, but you should keep them in mind when you are designing SmartDataObjects for your application. Perhaps the best general rule is that the smallest number of SDOs that will do the job is the best number. Extra SDOs for specialized purposes are likely to cause headaches later on, when you are trying to sort out why the application’s behavior is not consistent.